package com.edu.dao;

import com.edu.bean.Dish;
import com.edu.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DishDao {
    
    /**
     * 查询所有菜品
     */
    public List<Dish> findAll() {
        String sql = "SELECT * FROM dish ORDER BY dish_id DESC";
        List<Dish> dishes = new ArrayList<>();

        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                dishes.add(convertToDish(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return dishes;
    }
    
    /**
     * 根据ID查询菜品
     */
    public Dish findById(Integer dishId) {
        String sql = "SELECT * FROM dish WHERE dish_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, dishId);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                return convertToDish(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    
    /**
     * 添加菜品
     */
    public boolean add(Dish dish) {
        String sql = "INSERT INTO dish (dish_name, price, image_url, description, status, create_time) VALUES (?, ?, ?, ?, ?, NOW())";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, dish.getDishName());
            stmt.setBigDecimal(2, dish.getPrice());
            stmt.setString(3, dish.getImageUrl());
            stmt.setString(4, dish.getDescription());
            stmt.setInt(5, dish.getStatus());

            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
    
    /**
     * 更新菜品
     */
    public boolean update(Dish dish) {
        String sql = "UPDATE dish SET dish_name = ?, price = ?, description = ?, status = ?, update_time = NOW()";
        if (dish.getImageUrl() != null) {
            sql += ", image_url = ?";
        }
        sql += " WHERE dish_id = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, dish.getDishName());
            stmt.setBigDecimal(2, dish.getPrice());
            stmt.setString(3, dish.getDescription());
            stmt.setInt(4, dish.getStatus());

            if (dish.getImageUrl() != null) {
                stmt.setString(5, dish.getImageUrl());
                stmt.setInt(6, dish.getDishId());
            } else {
                stmt.setInt(5, dish.getDishId());
            }

            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
    
    /**
     * 删除菜品
     */
    public boolean delete(Integer dishId) {
        String sql = "DELETE FROM dish WHERE dish_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, dishId);
            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
    
    /**
     * 查询总记录数
     */
    public int findTotalCount() {
        String sql = "SELECT COUNT(*) FROM dish";
        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
    
    /**
     * 分页查询菜品
     * @param start 开始位置
     * @param pageSize 每页大小
     */
    public List<Dish> findByPage(int start, int pageSize) {
        String sql = "SELECT * FROM dish ORDER BY dish_id DESC LIMIT ?, ?";
        List<Dish> dishes = new ArrayList<>();

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, start);
            stmt.setInt(2, pageSize);
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                dishes.add(convertToDish(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return dishes;
    }
    
    /**
     * 根据菜品名称查询总记录数
     */
    public int findTotalCountByName(String dishName) {
        String sql = "SELECT COUNT(*) FROM dish WHERE dish_name LIKE ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, "%" + dishName + "%");
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }


    /**
     * 根据菜品名称分页查询
     */
    public List<Dish> findByPageAndName(String dishName, int start, int pageSize) {
        String sql = "SELECT * FROM dish WHERE dish_name LIKE ? ORDER BY dish_id DESC LIMIT ?, ?";
        List<Dish> dishes = new ArrayList<>();

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, "%" + dishName + "%");
            stmt.setInt(2, start);
            stmt.setInt(3, pageSize);
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                dishes.add(convertToDish(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return dishes;
    }
    
    /**
     * 将ResultSet转换为Dish对象
     */
    private Dish convertToDish(ResultSet rs) throws SQLException {
        Dish dish = new Dish();
        dish.setDishId(rs.getInt("dish_id"));
        dish.setDishName(rs.getString("dish_name"));
        dish.setPrice(rs.getBigDecimal("price"));
        dish.setImageUrl(rs.getString("image_url"));
        dish.setDescription(rs.getString("description"));
        dish.setStatus(rs.getInt("status"));
        dish.setCreateTime(rs.getTimestamp("create_time"));
        dish.setUpdateTime(rs.getTimestamp("update_time"));
        return dish;
    }

    /**
     * 用户端查询所有在售菜品
     */
    public List<Dish> userFindAllDishes() {
        String sql = "SELECT * FROM dish WHERE status = 1 ORDER BY create_time DESC";
        List<Dish> dishes = new ArrayList<>();

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {

            while (rs.next()) {
                dishes.add(convertToDish(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return dishes;
    }
    
    /**
     * 用户端根据ID查询在售菜品
     */
    public Dish userFindDishById(Integer dishId) {
        String sql = "SELECT * FROM dish WHERE dish_id = ? AND status = 1";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, dishId);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                return convertToDish(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}